package com.hethongviet.vending.helper.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.vending.Utils;
import com.hethongviet.vending.model.MachineInfo;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class MachineSQLite
{
	public static final String TABLE_NAME = "machine";

	public static final String COLUMN_ID_DB = "idDB";
	public static final String COLUMN_AMOUNT_BOX = "box_amount";
	public static final String COLUMN_PHONE_NUMBER = "phone_number";
	public static final String COLUMN_ADDRESS_PHYCICAL = "mac_address";
	public static final String COLUMN_ADDRESS_PHYCICAL_SERIAL = "mac_address_serial";
	public static final String COLUMN_DOMAIN_NAME = "domain_name";
	public static final String COLUMN_USERNAME_NETWORK = "username_network";
	public static final String COLUMN_PASSWORD_NETWORK = "password_network";
	public static final String COLUMN_DATABASE_NETWORK = "database_network";
	public static final String COLUMN_DOMAIN_PORT = "domain_port";

	public static final String COLUMN_CODE = "VendingMachineCode";
	public static final String COLUMN_DATE_CREATED = "DateCreate";
	public static final String COLUMN_DATE_TERMINATED = "DateTerminated";
	public static final String COLUMN_GROUP = "VendingMachineGroupID";
	public static final String COLUMN_LONGITUDE = "Longtitude";
	public static final String COLUMN_LATITTUDE = "Lattitude";
	public static final String COLUMN_STATUS = "StatusID";
	public static final String COLUMN_LOCATION_ID = "LocationID";
	public static final String COLUMN_STATUS_RENT = "StatusRent";
	public static final String COLUMN_STAFF_CREATE = "StaffCreate";
	public static final String COLUMN_STAFF_TERMINATE = "StaffTerminated";
	public static final String COLUMN_STAFF_UPDATE = "StaffUpdate";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;

	private String[] _allColumns = { COLUMN_ID_DB,

	COLUMN_ADDRESS_PHYCICAL, COLUMN_ADDRESS_PHYCICAL_SERIAL, COLUMN_AMOUNT_BOX,

	COLUMN_CODE, COLUMN_DATE_CREATED, COLUMN_DATE_TERMINATED,
			COLUMN_DOMAIN_NAME, COLUMN_USERNAME_NETWORK,
			COLUMN_PASSWORD_NETWORK, COLUMN_DATABASE_NETWORK,

			COLUMN_DOMAIN_PORT, COLUMN_GROUP, COLUMN_LATITTUDE,

			COLUMN_LOCATION_ID, COLUMN_LONGITUDE, COLUMN_PHONE_NUMBER,
			COLUMN_STAFF_CREATE,

			COLUMN_STAFF_TERMINATE, COLUMN_STAFF_UPDATE, COLUMN_STATUS,
			COLUMN_STATUS_RENT

	};

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "

			+ COLUMN_ADDRESS_PHYCICAL + " text, "
			+ COLUMN_ADDRESS_PHYCICAL_SERIAL + " text, " + COLUMN_AMOUNT_BOX
			+ " integer, "

			+ COLUMN_CODE + " text, " + COLUMN_DATE_CREATED + " integer, "
			+ COLUMN_DATE_TERMINATED + " integer, " + COLUMN_DOMAIN_NAME
			+ " text, " + COLUMN_USERNAME_NETWORK + " text, "
			+ COLUMN_PASSWORD_NETWORK + " text, " + COLUMN_DATABASE_NETWORK
			+ " text, "

			+ COLUMN_DOMAIN_PORT + " integer, " + COLUMN_GROUP + " integer, "
			+ COLUMN_LATITTUDE + " integer, "

			+ COLUMN_LOCATION_ID + " integer, " + COLUMN_LONGITUDE
			+ " integer, " + COLUMN_PHONE_NUMBER + " text, "
			+ COLUMN_STAFF_CREATE + " text, "

			+ COLUMN_STAFF_TERMINATE + " text, " + COLUMN_STAFF_UPDATE
			+ " text, " + COLUMN_STATUS + " integer, " + COLUMN_STATUS_RENT
			+ " integer";

	public MachineSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(MachineInfo machineInfo, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == machineInfo.getIdDB())
				return true;
		}
		return false;
	}

	/**
	 * @param machine
	 * @return row id -1: insert fail
	 */
	public long insert(MachineInfo machine)
	{
		Utils.debug("insert -- machineInfo : " + machine.getCode());
		open();

		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + machine.getIdDB(), null);

		long temp = -1;
		if (!dataExist(machine, cursor))
		{
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery(
					"INSERT INTO " + TABLE_NAME + "("

					+ COLUMN_ADDRESS_PHYCICAL + ", "
							+ COLUMN_ADDRESS_PHYCICAL_SERIAL + ", "
							+ COLUMN_AMOUNT_BOX + ", "

							+ COLUMN_CODE + ", " + COLUMN_DATE_CREATED + ", "
							+ COLUMN_DATE_TERMINATED + ", "
							+ COLUMN_DOMAIN_NAME + ", "
							+ COLUMN_USERNAME_NETWORK + ", "
							+ COLUMN_PASSWORD_NETWORK + ", "
							+ COLUMN_DATABASE_NETWORK + ", "

							+ COLUMN_DOMAIN_PORT + ", " + COLUMN_GROUP + ", "
							+ COLUMN_LATITTUDE + ", "

							+ COLUMN_LOCATION_ID + ", " + COLUMN_LONGITUDE
							+ ", " + COLUMN_PHONE_NUMBER + ", "
							+ COLUMN_STAFF_CREATE + ", "

							+ COLUMN_STAFF_TERMINATE + ", "
							+ COLUMN_STAFF_UPDATE + ", " + COLUMN_STATUS + ", "
							+ COLUMN_STATUS_RENT

							+ ") values( ?, ?, ?, " +

							"?, ?, ?, ?, ?, ?, ?, " +

							"?, ?, ?, " +

							"?, ?, ?, ?, " +

							"?, ?, ?, ? )",

					new String[] { machine.getAddressPhysical(),
							machine.getAddressPhysicalSerial(),
							machine.getBoxAmount() + "",

							machine.getCode(), machine.getDateCreated() + "",
							machine.getDateTerminated() + "",
							machine.getDomainName(),
							machine.getUsernameNetwork(),
							machine.getPasswordNetwork(),
							machine.getDatabaseNetwork(),

							machine.getDomainPort() + "",
							machine.getGroupId() + "",
							machine.getLatitude() + "",

							machine.getLocationId() + "",
							machine.getLongitude() + "",
							machine.getPhoneNumber(), machine.getStaffCreate(),

							machine.getStaffTerminate(),
							machine.getStaffUpdate(), machine.getStatus() + "",
							machine.getStatusRent() + "" });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast())
				temp = cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			Utils.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery(
					"UPDATE " + TABLE_NAME + " SET "

					+ COLUMN_ADDRESS_PHYCICAL + " = ?, "
							+ COLUMN_ADDRESS_PHYCICAL_SERIAL + " = ?, "
							+ COLUMN_AMOUNT_BOX + " = ?, "

							+ COLUMN_CODE + " = ?, " + COLUMN_DATE_CREATED
							+ " = ?, " + COLUMN_DATE_TERMINATED + " = ?, "
							+ COLUMN_DOMAIN_NAME + " = ?, "
							+ COLUMN_USERNAME_NETWORK + " = ?, "
									+ COLUMN_PASSWORD_NETWORK + " = ?, "
											+ COLUMN_DATABASE_NETWORK + " = ?, "

							+ COLUMN_DOMAIN_PORT + " = ?, " + COLUMN_GROUP
							+ " = ?, " + COLUMN_LATITTUDE + " = ?, "

							+ COLUMN_LOCATION_ID + " = ?, " + COLUMN_LONGITUDE
							+ " = ?, " + COLUMN_PHONE_NUMBER + " = ?, "
							+ COLUMN_STAFF_CREATE + " = ?, "

							+ COLUMN_STAFF_TERMINATE + " = ?, "
							+ COLUMN_STAFF_UPDATE + " = ?, " + COLUMN_STATUS
							+ " = ?, " + COLUMN_STATUS_RENT

							+ " = ? WHERE " + COLUMN_ID_DB + " = "
							+ machine.getIdDB(),

					new String[] { machine.getAddressPhysical(),
							machine.getAddressPhysicalSerial(),
							machine.getBoxAmount() + "",

							machine.getCode(), machine.getDateCreated() + "",
							machine.getDateTerminated() + "",
							machine.getDomainName(),
							machine.getUsernameNetwork(),
							machine.getPasswordNetwork(),
							machine.getDatabaseNetwork(),

							machine.getDomainPort() + "",
							machine.getGroupId() + "",
							machine.getLatitude() + "",

							machine.getLocationId() + "",
							machine.getLongitude() + "",
							machine.getPhoneNumber(), machine.getStaffCreate(),

							machine.getStaffTerminate(),
							machine.getStaffUpdate(), machine.getStatus() + "",
							machine.getStatusRent() + "" });

			if (!cursorUpdate.isAfterLast())
				temp = cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return temp;
	}

	public void deleteAll()
	{
		open();

		System.out.println("MachineInfo deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public List<MachineInfo> getAll()
	{
		open();

		List<MachineInfo> listMachines = new ArrayList<MachineInfo>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			MachineInfo machine = cursorToMachine(cursor);
			listMachines.add(machine);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listMachines;
	}

	private MachineInfo cursorToMachine(Cursor cursor)
	{
		MachineInfo machine = new MachineInfo();
		machine.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));

		machine.setAddressPhysical(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS_PHYCICAL)));
		machine.setAddressPhysicalSerial(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS_PHYCICAL_SERIAL)));
		machine.setBoxAmount(cursor.getInt(cursor.getColumnIndex(COLUMN_AMOUNT_BOX)));

		machine.setCode(cursor.getString(cursor.getColumnIndex(COLUMN_CODE)));
		machine.setDateCreated(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE_CREATED)));
		machine.setDateTerminated(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE_TERMINATED)));
		machine.setDomainName(cursor.getString(cursor.getColumnIndex(COLUMN_DOMAIN_NAME)));
		machine.setUsernameNetwork(cursor.getString(cursor.getColumnIndex(COLUMN_USERNAME_NETWORK)));
		machine.setPasswordNetwork(cursor.getString(cursor.getColumnIndex(COLUMN_PASSWORD_NETWORK)));
		machine.setDatabaseNetwork(cursor.getString(cursor.getColumnIndex(COLUMN_DATABASE_NETWORK)));

		machine.setDomainPort(cursor.getInt(cursor.getColumnIndex(COLUMN_DOMAIN_PORT)));
		machine.setGroupId(cursor.getInt(cursor.getColumnIndex(COLUMN_GROUP)));
		machine.setLatitude(cursor.getDouble(cursor.getColumnIndex(COLUMN_LATITTUDE)));

		machine.setLocationId(cursor.getInt(cursor.getColumnIndex(COLUMN_LOCATION_ID)));
		machine.setLongitude(cursor.getDouble(cursor.getColumnIndex(COLUMN_LONGITUDE)));
		machine.setPhoneNumber(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE_NUMBER)));
		machine.setStaffCreate(cursor.getString(cursor.getColumnIndex(COLUMN_STAFF_CREATE)));

		machine.setStaffTerminate(cursor.getString(cursor.getColumnIndex(COLUMN_STAFF_TERMINATE)));
		machine.setStaffUpdate(cursor.getString(cursor.getColumnIndex(COLUMN_STAFF_UPDATE)));
		machine.setStatus(cursor.getInt(cursor.getColumnIndex(COLUMN_STATUS)));
		machine.setStatusRent(cursor.getInt(cursor.getColumnIndex(COLUMN_STATUS_RENT)));

		return machine;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		isExist = cursor != null && cursor.getCount() > 0;

		cursor.close();
		close();

		return isExist;
	}
}
